use async_trait::async_trait;
use anyhow::Result;
use sqlx::{SqlitePool, Row};
use chrono::Utc;

use crate::database::models::{CreateUserRequest, LoginAttempt};
use shared::types::{User, UserPermission, Session, TransferLog, PermissionType};

#[async_trait]
pub trait UserRepository: Send + Sync {
    async fn create_user(&self, user: &CreateUserRequest) -> Result<User>;
    async fn create_user_with_hash(&self, username: &str, password_hash: &str, salt: &str, home_directory: &str, max_storage_size: i64) -> Result<User>;
    async fn get_user_by_username(&self, username: &str) -> Result<Option<User>>;
    async fn get_user_by_id(&self, id: i64) -> Result<Option<User>>;
    async fn update_user(&self, user: &User) -> Result<()>;
    async fn delete_user(&self, id: i64) -> Result<()>;
    async fn list_users(&self, limit: i64, offset: i64) -> Result<Vec<User>>;
}

#[async_trait]
pub trait SessionRepository: Send + Sync {
    async fn create_session(&self, session: &Session) -> Result<Session>;
    async fn get_session_by_token(&self, token: &str) -> Result<Option<Session>>;
    async fn update_session(&self, session: &Session) -> Result<()>;
    async fn delete_session(&self, id: i64) -> Result<()>;
    async fn cleanup_expired_sessions(&self) -> Result<u64>;
}

#[async_trait]
pub trait PermissionRepository: Send + Sync {
    async fn get_user_permissions(&self, user_id: i64) -> Result<Vec<UserPermission>>;
    async fn create_permission(&self, permission: &UserPermission) -> Result<()>;
    async fn delete_permission(&self, id: i64) -> Result<()>;
}

#[async_trait]
pub trait TransferLogRepository {
    async fn create_transfer_log(&self, log: &TransferLog) -> Result<i64>;
    async fn update_transfer_log(&self, id: i64, bytes_transferred: i64, status: &str) -> Result<()>;
    async fn get_user_transfer_logs(&self, user_id: i64, limit: i64, offset: i64) -> Result<Vec<TransferLog>>;
}

#[async_trait]
pub trait LoginAttemptRepository {
    async fn create_login_attempt(&self, attempt: &LoginAttempt) -> Result<()>;
    async fn get_recent_attempts(&self, client_ip: &str, minutes: i64) -> Result<Vec<LoginAttempt>>;
}

// 基础实现结构体
pub struct SqliteUserRepository {
    pool: SqlitePool,
}

impl SqliteUserRepository {
    pub fn new(pool: SqlitePool) -> Self {
        Self { pool }
    }
}

#[async_trait]
impl UserRepository for SqliteUserRepository {
    async fn create_user(&self, user: &CreateUserRequest) -> Result<User> {
        let now = chrono::Utc::now();
        
        // 这里假设CreateUserRequest中的password已经是哈希后的密码
        // 实际使用中需要先调用PasswordManager进行哈希
        let result = sqlx::query(
            "INSERT INTO users (username, password_hash, salt, home_directory, max_storage_size, created_at, updated_at)
             VALUES (?, ?, ?, ?, ?, ?, ?)"
        )
        .bind(&user.username)
        .bind(&user.password) // 这里应该是哈希后的密码
        .bind("") // salt需要单独处理
        .bind(&user.home_directory)
        .bind(user.max_storage_size.unwrap_or(1073741824))
        .bind(now)
        .bind(now)
        .execute(&self.pool)
        .await?;

        let user_id = result.last_insert_rowid();
        
        // 获取创建的用户
        self.get_user_by_id(user_id).await?.ok_or_else(|| anyhow::anyhow!("Failed to retrieve created user"))
    }
    
    async fn create_user_with_hash(&self, username: &str, password_hash: &str, salt: &str, home_directory: &str, max_storage_size: i64) -> Result<User> {
        let now = chrono::Utc::now();
        
        let result = sqlx::query(
            "INSERT INTO users (username, password_hash, salt, home_directory, max_storage_size, created_at, updated_at)
             VALUES (?, ?, ?, ?, ?, ?, ?)"
        )
        .bind(username)
        .bind(password_hash)
        .bind(salt)
        .bind(home_directory)
        .bind(max_storage_size)
        .bind(now)
        .bind(now)
        .execute(&self.pool)
        .await?;

        let user_id = result.last_insert_rowid();
        
        // 获取创建的用户
        self.get_user_by_id(user_id).await?.ok_or_else(|| anyhow::anyhow!("Failed to retrieve created user"))
    }
    
    async fn get_user_by_username(&self, username: &str) -> Result<Option<User>> {
        let row = sqlx::query(
            "SELECT id, username, password_hash, salt, home_directory, max_storage_size, is_active, 
                    created_at, updated_at, last_login, login_count
             FROM users WHERE username = ?"
        )
        .bind(username)
        .fetch_optional(&self.pool)
        .await?;

        if let Some(row) = row {
            Ok(Some(User {
                id: row.get("id"),
                username: row.get("username"),
                password_hash: row.get("password_hash"),
                salt: row.get("salt"),
                home_directory: row.get("home_directory"),
                max_storage_size: row.get("max_storage_size"),
                is_active: row.get("is_active"),
                created_at: row.get("created_at"),
                updated_at: row.get("updated_at"),
                last_login: row.get("last_login"),
                login_count: row.get("login_count"),
            }))
        } else {
            Ok(None)
        }
    }
    
    async fn get_user_by_id(&self, id: i64) -> Result<Option<User>> {
        let row = sqlx::query(
            "SELECT id, username, password_hash, salt, home_directory, max_storage_size, is_active, 
                    created_at, updated_at, last_login, login_count
             FROM users WHERE id = ?"
        )
        .bind(id)
        .fetch_optional(&self.pool)
        .await?;

        if let Some(row) = row {
            Ok(Some(User {
                id: row.get("id"),
                username: row.get("username"),
                password_hash: row.get("password_hash"),
                salt: row.get("salt"),
                home_directory: row.get("home_directory"),
                max_storage_size: row.get("max_storage_size"),
                is_active: row.get("is_active"),
                created_at: row.get("created_at"),
                updated_at: row.get("updated_at"),
                last_login: row.get("last_login"),
                login_count: row.get("login_count"),
            }))
        } else {
            Ok(None)
        }
    }
    
    async fn update_user(&self, user: &User) -> Result<()> {
        sqlx::query(
            "UPDATE users SET username = ?, password_hash = ?, salt = ?, home_directory = ?, 
                    max_storage_size = ?, is_active = ?, updated_at = ?, last_login = ?, login_count = ?
             WHERE id = ?"
        )
        .bind(&user.username)
        .bind(&user.password_hash)
        .bind(&user.salt)
        .bind(&user.home_directory)
        .bind(user.max_storage_size)
        .bind(user.is_active)
        .bind(Utc::now())
        .bind(&user.last_login)
        .bind(user.login_count)
        .bind(user.id)
        .execute(&self.pool)
        .await?;

        Ok(())
    }
    
    async fn delete_user(&self, id: i64) -> Result<()> {
        sqlx::query("DELETE FROM users WHERE id = ?")
            .bind(id)
            .execute(&self.pool)
            .await?;

        Ok(())
    }
    
    async fn list_users(&self, limit: i64, offset: i64) -> Result<Vec<User>> {
        let rows = sqlx::query(
            "SELECT id, username, password_hash, salt, home_directory, max_storage_size, is_active, 
                    created_at, updated_at, last_login, login_count
             FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?"
        )
        .bind(limit)
        .bind(offset)
        .fetch_all(&self.pool)
        .await?;

        let users = rows.into_iter().map(|row| User {
            id: row.get("id"),
            username: row.get("username"),
            password_hash: row.get("password_hash"),
            salt: row.get("salt"),
            home_directory: row.get("home_directory"),
            max_storage_size: row.get("max_storage_size"),
            is_active: row.get("is_active"),
            created_at: row.get("created_at"),
            updated_at: row.get("updated_at"),
            last_login: row.get("last_login"),
            login_count: row.get("login_count"),
        }).collect();

        Ok(users)
    }
}

// Session Repository实现
pub struct SqliteSessionRepository {
    pool: SqlitePool,
}

impl SqliteSessionRepository {
    pub fn new(pool: SqlitePool) -> Self {
        Self { pool }
    }
}

#[async_trait]
impl SessionRepository for SqliteSessionRepository {
    async fn create_session(&self, session: &Session) -> Result<Session> {
        let result = sqlx::query(
            "INSERT INTO sessions (user_id, session_token, client_ip, client_port, data_port, passive_port, 
                    transfer_mode, current_directory, created_at, last_activity, is_active)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
        )
        .bind(session.user_id)
        .bind(&session.session_token)
        .bind(&session.client_ip)
        .bind(session.client_port)
        .bind(session.data_port)
        .bind(session.passive_port)
        .bind(session.transfer_mode.to_string())
        .bind(&session.current_directory)
        .bind(session.created_at)
        .bind(session.last_activity)
        .bind(session.is_active)
        .execute(&self.pool)
        .await?;

        let session_id = result.last_insert_rowid();
        
        // 获取创建的会话
        let row = sqlx::query(
            "SELECT id, user_id, session_token, client_ip, client_port, data_port, passive_port, 
                    transfer_mode, current_directory, created_at, last_activity, is_active
             FROM sessions WHERE id = ?"
        )
        .bind(session_id)
        .fetch_one(&self.pool)
        .await?;

        Ok(Session {
            id: row.get("id"),
            user_id: row.get("user_id"),
            session_token: row.get("session_token"),
            client_ip: row.get("client_ip"),
            client_port: row.get("client_port"),
            data_port: row.get("data_port"),
            passive_port: row.get("passive_port"),
            transfer_mode: shared::types::TransferMode::from_string(&row.get::<String, _>("transfer_mode")),
            current_directory: row.get("current_directory"),
            created_at: row.get("created_at"),
            last_activity: row.get("last_activity"),
            is_active: row.get("is_active"),
        })
    }
    
    async fn get_session_by_token(&self, token: &str) -> Result<Option<Session>> {
        let row = sqlx::query(
            "SELECT id, user_id, session_token, client_ip, client_port, data_port, passive_port, 
                    transfer_mode, current_directory, created_at, last_activity, is_active
             FROM sessions WHERE session_token = ?"
        )
        .bind(token)
        .fetch_optional(&self.pool)
        .await?;

        if let Some(row) = row {
            Ok(Some(Session {
                id: row.get("id"),
                user_id: row.get("user_id"),
                session_token: row.get("session_token"),
                client_ip: row.get("client_ip"),
                client_port: row.get("client_port"),
                data_port: row.get("data_port"),
                passive_port: row.get("passive_port"),
                transfer_mode: shared::types::TransferMode::from_string(&row.get::<String, _>("transfer_mode")),
                current_directory: row.get("current_directory"),
                created_at: row.get("created_at"),
                last_activity: row.get("last_activity"),
                is_active: row.get("is_active"),
            }))
        } else {
            Ok(None)
        }
    }
    
    async fn update_session(&self, session: &Session) -> Result<()> {
        sqlx::query(
            "UPDATE sessions SET user_id = ?, session_token = ?, client_ip = ?, client_port = ?, 
                    data_port = ?, passive_port = ?, transfer_mode = ?, current_directory = ?, 
                    created_at = ?, last_activity = ?, is_active = ?
             WHERE id = ?"
        )
        .bind(session.user_id)
        .bind(&session.session_token)
        .bind(&session.client_ip)
        .bind(session.client_port)
        .bind(session.data_port)
        .bind(session.passive_port)
        .bind(session.transfer_mode.to_string())
        .bind(&session.current_directory)
        .bind(session.created_at)
        .bind(session.last_activity)
        .bind(session.is_active)
        .bind(session.id)
        .execute(&self.pool)
        .await?;

        Ok(())
    }
    
    async fn delete_session(&self, id: i64) -> Result<()> {
        sqlx::query("DELETE FROM sessions WHERE id = ?")
            .bind(id)
            .execute(&self.pool)
            .await?;

        Ok(())
    }
    
    async fn cleanup_expired_sessions(&self) -> Result<u64> {
        let result = sqlx::query(
            "DELETE FROM sessions WHERE is_active = 0 OR last_activity < datetime('now', '-24 hours')"
        )
        .execute(&self.pool)
        .await?;

        Ok(result.rows_affected())
    }
}

// Permission Repository实现
pub struct SqlitePermissionRepository {
    pool: SqlitePool,
}

impl SqlitePermissionRepository {
    pub fn new(pool: SqlitePool) -> Self {
        Self { pool }
    }
}

#[async_trait]
impl PermissionRepository for SqlitePermissionRepository {
    async fn get_user_permissions(&self, user_id: i64) -> Result<Vec<UserPermission>> {
        let rows = sqlx::query(
            "SELECT id, user_id, permission_type, path_pattern, granted, created_at
             FROM user_permissions WHERE user_id = ?"
        )
        .bind(user_id)
        .fetch_all(&self.pool)
        .await?;

        let permissions = rows.into_iter().map(|row| UserPermission {
            id: row.get("id"),
            user_id: row.get("user_id"),
            permission_type: PermissionType::from_string(&row.get::<String, _>("permission_type")),
            path_pattern: row.get("path_pattern"),
            granted: row.get("granted"),
            created_at: row.get("created_at"),
        }).collect();

        Ok(permissions)
    }
    
    async fn create_permission(&self, permission: &UserPermission) -> Result<()> {
        sqlx::query(
            "INSERT INTO user_permissions (user_id, permission_type, path_pattern, granted, created_at)
             VALUES (?, ?, ?, ?, ?)"
        )
        .bind(permission.user_id)
        .bind(permission.permission_type.to_string())
        .bind(&permission.path_pattern)
        .bind(permission.granted)
        .bind(permission.created_at)
        .execute(&self.pool)
        .await?;

        Ok(())
    }
    
    async fn delete_permission(&self, id: i64) -> Result<()> {
        sqlx::query("DELETE FROM user_permissions WHERE id = ?")
            .bind(id)
            .execute(&self.pool)
            .await?;

        Ok(())
    }
}